A CREATE FUNCTION(scalar):

- Specify a name for the function.

- data type for each input parameter.

- RETURNS keyword and the data type of the scalar return value.

Example : 

CREATE FUNCTION GetPrice (Vendor CHAR(20), Pid INT)          
    RETURNS  DECIMAL(10,3) 

    LANGUAGE SQL  

    MODIFIES SQL

    BEGIN 

      DECLARE price DECIMAL(10,3);

      IF Vendor = 'Vendor 1' 

        THEN SET price = (SELECT ProdPrice FROM V1Table WHERE Id = Pid); 

      ELSE IF Vendor = 'Vendor 2' 

        THEN SET price = (SELECT Price 

                          FROM V2Table 

             WHERE Pid = GetPrice.Pid); 

      END IF; 

  RETURN price; 

  END 

SELECT dbo.function (parameters)

Here we create a user-defined (Scalar-valued function) which remove html tag from string. It is a simple function that need a string parameter;

Example Of  SQL  Functions 

CREATE FUNCTION [dbo].[RemoveHtmlFronString] (@HTMLText VARCHAR(MAX))

RETURNS VARCHAR(MAX)

AS

BEGIN

DECLARE @Start INT

DECLARE @End INT

DECLARE @Length INT

SET @Start = CHARINDEX('<',@HTMLText) SET @End = 

CHARINDEX('>',@HTMLText,CHARINDEX('<',@HTMLText)) 

SET @Length = (@End - @Start) + 1 WHILE @Start > 0

AND @End > 0

AND @Length > 0

BEGIN

SET @HTMLText = STUFF(@HTMLText,@Start,@Length,'')

SET @Start = CHARINDEX('<',@HTMLText) SET @End = CHARINDEX('>',@HTMLText,CHARINDEX('<',@HTMLText))

SET @Length = (@End - @Start) + 1

END

RETURN LTRIM(RTRIM(@HTMLText))

END

--USE

 PRINT [dbo].[RemoveHtmlFronString]('<b> Hello</b>')